#! /usr/bin/env python3
# -*- coding: utf-8 -*-
#######################
#	获取新浪网站企业财报信息
#######################

import sys
import time
import urllib
import re
from bs4 import BeautifulSoup
import io
import os
dirs = os.path.abspath(os.path.dirname(__file__)+"/../Config")
os.sys.path.append(dirs)   #将上上级目录加载到python的环境变量中
from config import conn,webdriver,logpath
from multiprocessing import Pool
import random
import socket
sys.setrecursionlimit(1000000)  # set the maximum depth as 1500

IP = socket.gethostbyname(socket.gethostname())
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),'Began to crawl data...')

cur = conn.cursor()
currentyear = time.strftime("%Y", time.localtime())
years = [int(currentyear)-1,int(currentyear)-2,int(currentyear)-3]

time.sleep(5)
QQ = "JLP"
#获取远程URL链接
def GetUrlData(url):
	data=urllib.request.urlopen(url).read()
	return data

#递归获取页面内容 如果页面载入失败 重新加载
def DriverGet(url,tableid,eid,enterprise,name,times=1):
	phantomjspath = "D:/phantomjs%s/bin/phantomjs" % name
	# driver = webdriver.Firefox()
	driver = webdriver.PhantomJS(executable_path=phantomjspath)
	driver.implicitly_wait(60)
	js='window.open("%s")' % url
	driver.execute_script(js)
	handles = driver.window_handles # 获取当前窗口句柄集合（列表类型）
	driver.switch_to_window(handles[-1])
	# driver.get(url)
	
	time.sleep(10)
	soup = None
	soup = BeautifulSoup(driver.page_source,"html.parser")
	driver.close()
	driver.quit()
	if(soup.find("table",id=tableid) or soup.find("div",id=tableid)):		
		return soup
	else:
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Reload the page failed to open the page ",times," time...",name)		
		if(times < 10):
			times = times+1
			return DriverGet(url,tableid,eid,enterprise,times,name=name)
		else:
			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"current enterprise Information fetching failure...",name)
			#保存抓取失败企业
			insertsql = "insert into crawlerfalselog (eid,ename,url,type) values (%s,'%s','%s',1)" % (eid,enterprise,url)
			try:
				cur.execute(insertsql)
				conn.commit()
			except Exception as e:
				print("Data add failure",name)
			exit()
def getEid(fullname,times=1):
	#查询公司对应ID
	sqls = "select eid from enterpriseinfo where searchname like '%s'" % ("%"+fullname+"%")
	cur.execute(sqls)
	result = cur.fetchone()
	if result == None:
		if(times < 3):
			times = times+1
			filepath = sys.path[0]
			os.system("python "+filepath+"/enterprisesimple.py %s" % fullname)
			return getEid(fullname,times)
		else:
			return 0
	else:
		eid = result[0]
		return eid
#获取详请页面内容 处理
def dealData(name):
	searchname=""
	#查询一条未查找的记录
	sqlc = "select stockcode from enterpriseinshort where status=1 and full_ename not like '%银行%' limit 1"
	try:
		cur.execute(sqlc)
		stockcode = cur.fetchone()
		searchname = stockcode[0]
	except Exception as e:
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"All Completed. . .",name)
		exit()
	
	#加个状态锁
	sqlname = "update enterpriseinshort set status=2,IP='%s',QQ='%s' where stockcode='%s'" % (IP,QQ,searchname)
	cur.execute(sqlname)
	conn.commit()
	#加个状态锁
	
	print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"get sina company code...",name)
	url = "http://suggest3.sinajs.cn/suggest/type=&key=%s" % urllib.request.quote(searchname)
	geturlData = GetUrlData(url).decode("gbk")
	enterprisecode = geturlData.split("=")[1].split(",")[2]
	enterprisestockcode = geturlData.split("=")[1].split(",")[3]
	print(enterprisestockcode)
	
	#查询公司在本行业内的排名
	print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the",enterprisestockcode,"company homepage",name)
	soup = DriverGet("http://finance.sina.com.cn/realstock/company/%s/nc.shtml" % enterprisestockcode,"rankSummary_l",eid=0,enterprise=searchname,name=name)
	rankSummary = ""
	try:
		sy = soup.find("div",id="rankSummary_l").find_all("span")
		sylist = sy[2].text.strip()
		pattern = re.compile("第(\d*)\((\d*)家\)")
		res = pattern.search(sylist).groups()
		rankSummary = "%s/%s" % (res[0],res[1])
	except Exception as e:
		print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"No rank..",name)
	##########################
	fullname = soup.find("div",class_="com_overview").find_all("p")[1].text.strip()
	shortname = soup.find("h1",id="stockName").text.strip()
	shortname = shortname.split("(")[0]
	eid = getEid(fullname)
	if eid == 0:
		return dealData(name)
	try:
		for year in years:
			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"get ",year," year data",name)
			#############################################################财务指标
			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company financial index page",name)
			soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_FinancialGuideLine/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"BalanceSheetNewTable0",eid=eid,enterprise=searchname,name=name)
			cwzb_trlist = soup.find("table",id="BalanceSheetNewTable0").find_all("tr")
			#去掉表头
			cwzb={}
			for cwzb_tr in cwzb_trlist[1:]:
				cwzb_tdlist = cwzb_tr.find_all("td")
				if(len(cwzb_tdlist)>=2):
					key = cwzb_tdlist[0]
					value = cwzb_tdlist[1]
					cwzb[key.text.strip()] = value.text.strip().replace(',','').replace('--','0')

			#############################################################资产负债表
			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company's balance sheet page",name)
			soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"BalanceSheetNewTable0",eid=eid,enterprise=searchname,name=name)
			zcfz_trlist = soup.find("table",id="BalanceSheetNewTable0").find_all("tr")
			#去掉表头
			zcfz={}
			for zcfz_tr in zcfz_trlist[1:]:
				zcfz_tdlist = zcfz_tr.find_all("td")
				if(len(zcfz_tdlist)>=2):
					key = zcfz_tdlist[0]
					value = zcfz_tdlist[1]
					zcfz[key.text.strip()] = value.text.strip().replace(',','').replace('--','0')


			#############################################################利润表
			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company profit page",name)
			soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_ProfitStatement/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"ProfitStatementNewTable0",eid=eid,enterprise=searchname,name=name)
			lr_trlist = soup.find("table",id="ProfitStatementNewTable0").find_all("tr")
			#去掉表头
			lr={}
			for lr_tr in lr_trlist[1:]:
				lr_tdlist = lr_tr.find_all("td")
				if(len(lr_tdlist)>=2):
					key = lr_tdlist[0]
					value = lr_tdlist[1]
					lr[key.text.strip()] = value.text.strip().replace(',','').replace('--','0')

			#############################################################现金流量表
			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Open the company's cash flow statement page",name)
			soup = DriverGet("http://money.finance.sina.com.cn/corp/go.php/vFD_CashFlow/stockid/%s/ctrl/%s/displaytype/4.phtml" % (enterprisecode,year),"ProfitStatementNewTable0",eid=eid,enterprise=searchname,name=name)
			xjll_trlist = soup.find("table",id="ProfitStatementNewTable0").find_all("tr")
			#去掉表头
			xjll={}
			for xjll_tr in xjll_trlist[1:]:
				xjll_tdlist = xjll_tr.find_all("td")
				if(len(xjll_tdlist)>=2):
					key = xjll_tdlist[0]
					value = xjll_tdlist[1]
					xjll[key.text.strip()] = value.text.strip().replace(',','').replace('--','0')

			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Data mining...",name)

			dqgxzw = float(zcfz["短期借款"])+float(zcfz["交易性金融负债"])+float(zcfz["应付票据"])+float(zcfz["一年内到期的非流动负债"])+float(zcfz["应付短期债券"])
			if(dqgxzw == 0):
				kzyzpxjldqgxzwb = 0
			else:
				kzyzpxjldqgxzwb = format((float(xjll["经营活动产生的现金流量净额"])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"]))/dqgxzw,'.2f')
			if(lr["利息支出"] == "0"):
				kzyzpxjlllxbzbs = 0
				elxbzbs = 0
			else:
				kzyzpxjlllxbzbs = format((float(xjll["经营活动产生的现金流量净额"])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"]))/float(lr["利息支出"]),'.2f')
				elxbzbs = format(float(lr["三、营业利润"])/float(lr["利息支出"]), '.2f')
			ybxzchj = format(float(zcfz["流动资产合计"])/float(zcfz["资产总计"])*100, '.2f')
			#kzyzpxjll = format(float(xjll["经营活动产生的现金流量净额"])+float(xjll["投资活动产生的现金流量净额"]), '.2f')
			kzyzpxjll = format(float(xjll["经营活动产生的现金流量净额"])-float(xjll["购建固定资产、无形资产和其他长期资产所支付的现金"]), '.2f')
			#添加 行业排名 cwzb["存货周转率(次)"] xjll['经营活动现金流入小计'] cwzb['净资产收益率']
			gxzw = format(dqgxzw + float(zcfz["长期借款"]) + float(zcfz["应付债券"]), '.2f')
				
			sql = "replace into enterprisefinance (`eid`,`fullname`,`ename`,`stockcode`,`year`,`gdzc`,`jyxjrzc`,`hbzj`,`qtzc`,`ybxzchj`,`zzc`,`dqjk`,`yfpj`,`ynndqdfldfz`,`ldfzhj`,`cqjk`,`fldfzhj`,`fzhj`,`sszb`,`syzjchj`,`syl`,`yysr`,`qjfy`,`jlr`,`jyhdcsdxjllje`,`gjcqzjszfdxj`,`czcqzjszfdxj`,`kzyzpxjll`,`czhdjxjl`,`EBIT`,`dqgxzw`,`gxzw`,`mll`,`zcfzl`,`kzyzpxjlllxbzbs`,`kzyzpxjldqgxzwb`,`elxbzbs`,`xjhll`,`dbbl`,`chzzl`,`jyhdxjlrxj`,`jzcsyl`,`hypm`) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (eid,fullname,shortname,searchname,year,zcfz["非流动资产合计"],zcfz["交易性金融资产"],zcfz["货币资金"],"",ybxzchj,zcfz["资产总计"],zcfz["短期借款"],zcfz["应付票据"],zcfz["一年内到期的非流动负债"],zcfz["流动负债合计"],zcfz["长期借款"],zcfz["非流动负债合计"],zcfz["负债合计"],zcfz["实收资本(或股本)"],zcfz["所有者权益(或股东权益)合计"],"",lr["营业收入"],lr["二、营业总成本"],lr["四、净利润"],xjll["经营活动产生的现金流量净额"],xjll["购建固定资产、无形资产和其他长期资产所支付的现金"],xjll["处置固定资产、无形资产和其他长期资产所收回的现金净额"],kzyzpxjll,xjll["筹资活动产生的现金流量净额"],lr["利润总额"],format(dqgxzw, '.2f'),gxzw,format(float(cwzb["营业利润率(%)"]), '.2f'),format(float(cwzb["资产负债率(%)"]), '.2f'),kzyzpxjlllxbzbs,kzyzpxjldqgxzwb,elxbzbs,"","",format(float(cwzb["存货周转率(次)"]), '.2f'),xjll['经营活动现金流入小计'],format(float(cwzb['净资产收益率(%)']), '.2f'),rankSummary)

			print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),"Corporate earnings data warehousing...",name)
			try:
				cur.execute(sql)
				conn.commit()
			except Exception as e:
				print("Data add failure",name)
		#解个状态锁 到完成状态
		sqlname = "update enterpriseinshort set status=3 where stockcode='%s'" % searchname
		cur.execute(sqlname)
		conn.commit()
		#解个状态锁 到完成状态
	except Exception as e:			
		#解个状态锁 到初始状态
		sqlname = "update enterpriseinshort set status=1,IP='',QQ='' where stockcode='%s'" % searchname
		cur.execute(sqlname)
		conn.commit()
		#解个状态锁 到初始状态
	return dealData(name)
	#########数据处理完
dealData(0)
exit()
def long_time_task(name,):
	print('Run task %s (%s)...' % (name, os.getpid()))
	start = time.time()
	time.sleep(random.random() * 3)
	end = time.time()
	print('Task %s runs %0.2f seconds.' % (name, (end - start)))
	dealData(name)

if __name__=='__main__':
	print('Parent process %s.' % os.getpid())
	p = Pool(4)
	for i in range(4):
	    p.apply_async(long_time_task, args=(i,))
	print('Waiting for all subprocesses done...')
	p.close()
	p.join()
	print('All subprocesses done.')
